//  Copyright (c) 2010 Mahesh Sharma,Matt MacDonald
//
//  Permission is hereby granted, free of charge, to any person obtaining a copy
//  of this software and associated documentation files (the "Software"), to deal
//  in the Software without restriction, including without limitation the rights
//  to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
//  copies of the Software, and to permit persons to whom the Software is
//  furnished to do so, subject to the following conditions:
//
//  The above copyright notice and this permission notice shall be included in
//  all copies or substantial portions of the Software.
//
//  THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
//  IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
//  FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
//  AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
//  LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
//  OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
//  THE SOFTWARE.

package org.prx.prp.controller;

import java.util.ArrayList;
import java.util.Date;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import org.prx.prp.model.BillboardAPI;
import org.prx.prp.model.Scheduling;
import org.prx.prp.utility.DatabaseAdapter;
import org.prx.prp.utility.HttpRetriever;
import org.prx.prp.utility.XMLParser;

import android.database.Cursor;
import android.util.Log;


public class SchedulingManager {
  
  static SchedulingManager schedulingManager;
  public static SchedulingManager getInstance()
  {
	  if(schedulingManager==null)
		  schedulingManager=new SchedulingManager();
	  return schedulingManager;
  }
  
  

  private SchedulingManager()
  {
	  try {
		  //TODO : Add locationManager stuff here!		
		  DatabaseAdapter.createTable("CREATE TABLE IF NOT EXISTS SCHEDULINGS (ID INTEGER PRIMARY KEY,STREAM_ID INTEGER,PROGRAM_ID INTEGER,START_TIME,END_TIME);");
		  DatabaseAdapter.deleteAllRows("SCHEDULINGS");
		  findAllByTime();				
	  } catch (Exception e) {
		  // TODO Auto-generated catch block
		  e.printStackTrace();
	  }	
  }

  public static boolean syncSuccesful()
  {
	  String query="SELECT COUNT(*) AS SCH_COUNT FROM SCHEDULINGS";
	  Cursor c=DatabaseAdapter.executeQuery(query, null);
	  c.moveToFirst();
	  int count=c.getInt(c.getColumnIndex("SCH_COUNT"));
	  return (count > 0);
  }

  private void findAllByTime()
  {
	  Date d= new Date();
	  long scheduled_at=d.getTime()/1000;
	  try {
		  String url=BillboardAPI.BILLBOARD_MODEL_URL+"schedulings.xml?scheduled_at="+scheduled_at;
		  XMLParser.sync("schedulings","scheduling", url,BillboardAPI.SCHEDULES_MODEL_FIELDS);
	  } catch (Exception e) {
		  // TODO Auto-generated catch block
		  Log.d("PRPAND", "There was a problem with syncing data from billboard");
		  Log.d("PRPAND", e.getMessage());
	  }		
  }

  public ArrayList<Scheduling> findAllByStream(int streamId ,int hours) throws JSONException
	{
	  Date d=new Date();
	  long start=(d.getTime()/1000);
	  long end=start+hours*3600;	
	
		JSONArray result=HttpRetriever.getArrayData(BillboardAPI.BILLBOARD_MODEL_URL+"streams/"+streamId+"/schedulings.json?scheduled_start="+start+"&scheduled_end="+end);
		ArrayList<Scheduling> results=new ArrayList<Scheduling>(result.length());
		for(int index=0;index<result.length();index++)
		{			
		  JSONObject schedule=((JSONObject) result.get(index)).getJSONObject("scheduling");
		  results.add(new Scheduling(schedule));
		}
		return results;
	
		// TODO Auto-generated catch block
	
	}
  
  public Cursor findSchedulingsByDistance(double lowerLimit,double upperLimit)
  {
	  String query="SELECT STREAMS.DISPLAY_NAME AS STREAM_DISPLAY_NAME,STREAM_ID,SCHEDULINGS.ID as _id," + getFormattedTimeQuery("start_time") + "||' to '||" + getFormattedTimeQuery("end_time") + " AS TIME,PROGRAMS.TITLE AS PROGRAM_TITLE,PROGRAM_ID FROM SCHEDULINGS JOIN STREAMS ON STREAMS.ID= SCHEDULINGS.STREAM_ID JOIN PROGRAMS ON PROGRAMS.ID = SCHEDULINGS.PROGRAM_ID join current_distance on streams.source_id = current_distance.source_id where current_distance.distance<"+upperLimit+" and current_distance.distance>"+lowerLimit+" GROUP BY STREAM_ID ORDER BY current_distance.distance DESC";
	  Log.d("PRPAND",query);
	  return DatabaseAdapter.executeQuery(query,null);
  }

  public Cursor findFeaturedSchedulings()
  {
	  String query="SELECT trim(STREAMS.DISPLAY_NAME) AS STREAM_DISPLAY_NAME,STREAM_ID,SCHEDULINGS.ID as _id, " + getFormattedTimeQuery("start_time") + " || ' to ' || " + getFormattedTimeQuery("end_time") +" AS TIME, trim(PROGRAMS.TITLE) AS PROGRAM_TITLE,PROGRAM_ID FROM SCHEDULINGS JOIN STREAMS ON STREAMS.ID= SCHEDULINGS.STREAM_ID JOIN PROGRAMS ON PROGRAMS.ID = SCHEDULINGS.PROGRAM_ID GROUP BY STREAM_ID HAVING STREAM_ID IN ( SELECT STREAM_ID FROM STREAMS JOIN STREAM_LISTINGS ON STREAMS.ID=STREAM_LISTINGS.STREAM_ID WHERE STREAM_LIST_ID='1')";
	
	  return DatabaseAdapter.executeQuery(query,null);	
  }

  public Cursor findSchedulingsByState(String stateName)
  {
	  String query="SELECT STREAMS.DISPLAY_NAME AS STREAM_DISPLAY_NAME,STREAM_ID,SCHEDULINGS.ID as _id," + getFormattedTimeQuery("start_time") + "||' to '|| " + getFormattedTimeQuery("end_time") + " AS TIME,PROGRAMS.TITLE AS PROGRAM_TITLE,PROGRAM_ID FROM SCHEDULINGS JOIN STREAMS ON STREAMS.ID= SCHEDULINGS.STREAM_ID JOIN PROGRAMS ON PROGRAMS.ID = SCHEDULINGS.PROGRAM_ID JOIN SOURCES ON STREAMS.SOURCE_ID=SOURCES.ID WHERE UPPER(STATE_PRIMARY) = UPPER('"+stateName+"')";
	  return DatabaseAdapter.executeQuery(query,null);
  }
  
  private String getFormattedTimeQuery(String column){
	  String localTime = localTime(column);
	  
	  return "(CASE CAST(strftime('%H', "+ localTime +") AS INTEGER) >= 12 WHEN 1 THEN ( CASE CAST(strftime('%H', " + localTime +") AS INTEGER) = 12 WHEN 1 THEN( strftime('%H:%M', " + localTime+" ) || 'PM' ) ELSE(" + 
	  "CAST(CAST(strftime('%H', " + localTime +") AS INTEGER) - 12 AS TEXT)  || ':'  || strftime('%M', "+localTime+") || 'PM') END)"+
	  "ELSE( CASE CAST(strftime('%H', " + localTime+") AS INTEGER) = 0 WHEN 1 THEN ('12:' || strftime('%M', " + localTime+ ") || 'AM')"+
	  "ELSE(CAST(strftime('%H', " + localTime + ") AS INTEGER) || strftime(':%M', " + localTime + ") || 'AM') END) END)";
  }
  
  public Cursor findSchedulingsByProgramId(int programId){
	  String query = "SELECT STREAMS.DISPLAY_NAME AS STREAM_DISPLAY_NAME,STREAM_ID,SCHEDULINGS.ID as _id," + getFormattedTimeQuery("START_TIME") + " || ' to '|| " + getFormattedTimeQuery("END_TIME") + " AS TIME,PROGRAMS.TITLE AS PROGRAM_TITLE,PROGRAM_ID FROM SCHEDULINGS JOIN STREAMS ON STREAMS.ID= SCHEDULINGS.STREAM_ID JOIN PROGRAMS ON PROGRAMS.ID=SCHEDULINGS.PROGRAM_ID WHERE PROGRAM_ID = ? GROUP BY STREAM_ID";
	  return DatabaseAdapter.executeQuery(query, new String[]{ new Integer(programId).toString() });
  }
  
  private String localTime(String column){
	  return "time("+column+", 'localtime')";
  }
}
